import { query } from './database';
import bcrypt from 'bcryptjs';

// 创建用户表
const createUserTable = async () => {
  try {
    const sql = `
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('user', 'admin') DEFAULT 'user',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `;
    await query(sql);
    console.log('Users table created or already exists');
  } catch (error) {
    console.error('创建用户表失败:', error);
    throw error;
  }
};

// 创建分类表
const createCategoryTable = async () => {
  try {
    const sql = `
      CREATE TABLE IF NOT EXISTS categories (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `;
    await query(sql);
    console.log('Categories table created or already exists');
  } catch (error) {
    console.error('创建分类表失败:', error);
    throw error;
  }
};

// 创建单词表
const createWordTable = async () => {
  try {
    const sql = `
      CREATE TABLE IF NOT EXISTS words (
        id INT PRIMARY KEY AUTO_INCREMENT,
        category_id INT,
        word VARCHAR(100) NOT NULL,
        translation TEXT NOT NULL,
        example TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
      )
    `;
    await query(sql);
    console.log('Words table created or already exists');
  } catch (error) {
    console.error('创建单词表失败:', error);
    throw error;
  }
};

// 创建学习记录表
const createLearningRecordTable = async () => {
  try {
    const sql = `
      CREATE TABLE IF NOT EXISTS word_learning_records (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        word_id INT NOT NULL,
        status VARCHAR(20) NOT NULL,
        accuracy FLOAT,
        review_count INT DEFAULT 0,
        last_review_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
      )
    `;
    await query(sql);
    console.log('Word learning records table created or already exists');
  } catch (error) {
    console.error('创建学习记录表失败:', error);
    throw error;
  }
};

// 插入初始管理员用户
const insertAdminUser = async () => {
  try {
    const hashedPassword = await bcrypt.hash('admin123', 10);
    const sql = `
      INSERT IGNORE INTO users (username, password, role)
      VALUES ('admin', ?, 'admin')
    `;
    await query(sql, [hashedPassword]);
    console.log('Admin user created or already exists');
  } catch (error) {
    console.error('创建管理员用户失败:', error);
    throw error;
  }
};

// 插入示例分类
const insertSampleCategories = async () => {
  try {
    const categories = [
      { name: '基础词汇', description: '日常基础英语词汇' },
      { name: '商务英语', description: '商务场景常用词汇' },
      { name: '旅游英语', description: '旅游场景常用词汇' }
    ];

    for (const category of categories) {
      const sql = `
        INSERT IGNORE INTO categories (name, description)
        VALUES (?, ?)
      `;
      await query(sql, [category.name, category.description]);
    }
    console.log('Sample categories created or already exist');
  } catch (error) {
    console.error('创建示例分类失败:', error);
    throw error;
  }
};

// 插入示例单词
const insertSampleWords = async () => {
  try {
    const words = [
      { category_id: 1, word: 'hello', translation: '你好', example: 'Hello, how are you?' },
      { category_id: 1, word: 'world', translation: '世界', example: 'Hello, world!' },
      { category_id: 2, word: 'meeting', translation: '会议', example: 'We have a meeting at 2 PM.' },
      { category_id: 3, word: 'ticket', translation: '票', example: 'I need to buy a train ticket.' }
    ];

    for (const word of words) {
      const sql = `
        INSERT IGNORE INTO words (category_id, word, translation, example)
        VALUES (?, ?, ?, ?)
      `;
      await query(sql, [word.category_id, word.word, word.translation, word.example]);
    }
    console.log('Sample words created or already exist');
  } catch (error) {
    console.error('创建示例单词失败:', error);
    throw error;
  }
};

// 初始化数据库
export const initializeDatabase = async () => {
  try {
    // 按顺序创建表
    await createUserTable();
    await createCategoryTable();
    await createWordTable();
    await createLearningRecordTable();

    // 插入初始数据
    await insertAdminUser();
    await insertSampleCategories();
    await insertSampleWords();

    console.log('数据库初始化完成');
  } catch (error) {
    console.error('数据库初始化失败:', error);
    throw error;
  }
}; 